How to Connect Google Sheets to HTML Form
People who create small websites for college assignments or basic business websites all share a common requirement that involves collecting form data and storing it securely. Most people think you need a database and server for this. But you don’t actually need those things. 😊
The most straightforward and costless solution lets you link your HTML form with Google Sheets.
The first time I attempted this was for my event registration page. I had no backend coding knowledge which made database setup seem like an impossible task. Google Sheets together with Google Apps Script became my solution to work problems that I had. The system functioned like magic which enabled me to finish my tasks faster. I will demonstrate to you the same method through a simple language tutorial which breaks down the process into distinct steps.
This guide provides an ideal starting point for beginner students and readers without technical background.
Why Use Google Sheets with an HTML Form?
Before we start, let’s understand why this method is popular.
- The service costs nothing to use.
- The service requires no hosting and database configuration.
- Users can effortlessly access all data through three functions, which allow them to view, edit, and download information.
- The solution functions effectively for websites and projects that have minimal requirements.
- The solution functions perfectly for all contact form and feedback form and registration form needs.
The experience feels familiar to users who already work with Google Sheets.
What You Need Before Starting?
The requirements are minimal so you should not feel concerned.
- A Google account
- Basic knowledge of HTML
- Internet connection
- 15–20 minutes of free time
The requirements include only basic coding abilities. The requirements include only basic coding abilities. The requirements include only basic coding abilities. The requirements include only basic coding abilities.
Step 1: Create a Google Sheet
1. Go to Google Sheets and create a new blank sheet.
2. In first row, add headings like this:
These headings will help you to identify the data
Step 2: Open Google Apps Script
- Click on Extensions in your Google Sheet.
- Select Apps Script.
- A new tab will open with some default code visible.
- You should then delete the original code and make the file bare.
Step 3: Add Script to Receive Form Data
Now copy and paste this simple script. This script helps Google Sheets accept data from HTML form.
var sheetName
= 'Sheet1'
var scriptProp
= PropertiesService.getScriptProperties()
function intialSetup
() {
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}
function doPost (e) {
var lock = LockService.getScriptLock()
lock.tryLock(10000)
try {
var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
var sheet = doc.getSheetByName(sheetName)
var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
var nextRow = sheet.getLastRow() + 1
var newRow = headers.map(function(header) {
return header === 'timestamp'
? new Date() : e.parameter[header]
})
sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])
return ContentService
.createTextOutput(JSON.stringify({
'result': 'success', 'row': nextRow }))
.setMimeType(ContentService.MimeType.JSON)
}
catch (e) {
return ContentService
.createTextOutput(JSON.stringify({
'result': 'error', 'error': e }))
.setMimeType(ContentService.MimeType.JSON)
}
finally {
lock.releaseLock()
}
}
You don’t need to understand every single line. Just see the process for now.
Step 4: Deploy the Script as Web Application
The following procedure enables your HTML form to access your Google Sheet.
- You need to select Deploy from the menu and then choose New deployment.
- You need to select Web app as your application type.
- You need to configure the following settings.
Execute as: Me
Who has access: Anyone
Finally, Click Deploy
- The user needs to grant permission rights.
- The Web App URL needs to be copied.
This URL is very important. Save it in a secure location.
Step 5: Create Your HTML Form
Now let’s create a simple HTML form. You can copy > paste the following code.
<form id="submit-to-google-sheet" method="post">
<div class="form-group">
<label for="name">Name:</label>
<input
class="form-control"
type="text"
name="name"
id="name"
placeholder="Name"
required
/>
</div>
<div class="form-group">
<label for="email">Email:</label>
<input
class="form-control"
type="email"
name="email"
id="email"
placeholder="Email"
required
/>
</div>
<div class="form-group">
<label for="slip">Contact Number:</label>
<input
class="form-control"
type="text"
name="contact_number"
id="contact_number"
placeholder="Contact Number"
required
/>
</div>
<div class="form-group">
<label for="loc">Gender:</label>
<select class="form-control" name="gender" id="gender" required>
<option value="" disabled selected hidden>Choose...</option>
<option value="male">Male</option>
<option value="female">Females</option>
</select>
</div>
<div class="form-group">
<label for="loc">Password</label>
<input
type="password"
class="form-control"
name="password"
id="message"
placeholder="Password"
required
></textarea>
<div class="form-group form-check mt-2">
<input class="form-check-input" type="checkbox" id="age" required />
<label class="form-check-label" for="age"
>Check if you are over 18 years old</label
>
</div>
<div class="form-group form-check">
<input class="form-check-input" type="checkbox" id="ex" required />
<label class="form-check-label" for="ex"
>By checking this box I certify that I am agree with the data
terms and policies.
</label>
</div>
<button type="submit" class="btn btn-primary btn-block">
Create Account
</button>
</form>
<script>
const scriptURL =
"PASTE YOUR WEB APP URL HERE";
const form = document.forms["submit-to-google-sheet"];
form.addEventListener("submit", (e) => {
e.preventDefault();
var formData = new FormData(form);
var ex = document.getElementById("ex").checked;
var age = document.getElementById("age").checked;
if (age) {
formData.append("age", "Yes");
} else {
formData.append("age", "No");
}
if (ex) {
formData.append("ex", "Yes");
} else {
formData.append("ex", "No");
}
fetch(scriptURL, { method: "POST", body: formData })
.then((response) => {
swal("Done", "Submitted Successfully.", "success");
})
.catch((error) => {
swal("Error", "Something went wrong. please try again!", "error");
});
});
</script>
Replace PASTE YOUR WEB APP URL HERE (in <script> tag) with the URL you copied earlier.
Step 6: Test Everything
- Open your HTML file in a browser
- Fill the form
- Click Submit
- Open Google Sheets
Common Problems and Easy Fixes🛠️⚙️
Is the form submission process malfunctioning?
1. You need to verify that the Web App URL has been entered correctly.
2. You need to check that access permissions are configured to allow "Anyone" to access the system.
The data fails to show up in Sheets?
1. You need to confirm that the script requires the column order which matches your current setup.
2. You need to review the access rights a second time.
Error message?
redeploy the web application.
When Should You Use This Method?
The optimal solution for this situation works best with the following applications:
- College projects
- Small business websites
- Contact forms
- Feedback forms
- Event registrations
The database solution becomes essential for websites that handle multiple users who visit thousands of pages. The current system provides sufficient capacity for most entry-level users at this point.
Final Thoughts🧠✨
The easiest and most effective method for gathering online information requires Google Sheet integration with HTML forms. The process requires only basic Google account access and users need to wait for their results.
Please share this article on social media with your friends and classmates because it contains useful information. The information might help someone who needs to complete their work in less time.
Goodbye for now because I will return to you in the next article... 🚀
0 Comments